<?php

namespace app\admin\controller;

use app\admin\library\CountDashboard;
use app\admin\model\Admin;
use app\admin\model\User;
use app\common\controller\Backend;
use app\common\model\Attachment;
use fast\Date;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\Db;
use think\Request;

/**
 * 控制台
 *
 * @icon   fa fa-dashboard
 * @remark 用于展示当前系统中的统计数据、统计报表及重要实时数据
 */
class Dashboard extends Backend
{

    /**
     * 查看
     */
    public function index()
    {
        try {
            \think\Db::execute("SET @@sql_mode='';");
        } catch (\Exception $e) {

        }
        $column = [];
        $starttime = Date::unixtime('day', -6);
        $endtime = Date::unixtime('day', 0, 'end');
        $joinlist = Db("user")->where('jointime', 'between time', [$starttime, $endtime])
            ->field('jointime, status, COUNT(*) AS nums, DATE_FORMAT(FROM_UNIXTIME(jointime), "%Y-%m-%d") AS join_date')
            ->group('join_date')
            ->select();
        for ($time = $starttime; $time <= $endtime;) {
            $column[] = date("Y-m-d", $time);
            $time += 86400;
        }
        $userlist = array_fill_keys($column, 0);
        foreach ($joinlist as $k => $v) {
            $userlist[$v['join_date']] = $v['nums'];
        }

        $dbTableList = Db::query("SHOW TABLE STATUS");
        $addonList = get_addon_list();
        $totalworkingaddon = 0;
        $totaladdon = count($addonList);
        foreach ($addonList as $index => $item) {
            if ($item['state']) {
                $totalworkingaddon += 1;
            }
        }
        $res = [
            'totaluser' => User::count(),
            'totaladdon' => $totaladdon,
            'totaladmin' => Admin::count(),
            'totalcategory' => \app\common\model\Category::count(),
            'todayusersignup' => User::whereTime('jointime', 'today')->count(),
            'todayuserlogin' => User::whereTime('logintime', 'today')->count(),
            'sevendau' => User::whereTime('jointime|logintime|prevtime', '-7 days')->count(),
            'thirtydau' => User::whereTime('jointime|logintime|prevtime', '-30 days')->count(),
            'threednu' => User::whereTime('jointime', '-3 days')->count(),
            'sevendnu' => User::whereTime('jointime', '-7 days')->count(),
            'dbtablenums' => count($dbTableList),
            'dbsize' => array_sum(array_map(function ($item) {
                return $item['Data_length'] + $item['Index_length'];
            }, $dbTableList)),
            'totalworkingaddon' => $totalworkingaddon,
            'attachmentnums' => Attachment::count(),
            'attachmentsize' => Attachment::sum('filesize'),
            'picturenums' => Attachment::where('mimetype', 'like', 'image/%')->count(),
            'picturesize' => Attachment::where('mimetype', 'like', 'image/%')->sum('filesize'),
        ];

        //会员统计相关
        $user_group['user_group_count'] = CountDashboard::getUserGroupCount(request()->param());

        //内容分类+文章统计相关
        $client['client_list'] = [
//            CountDashboard::getclient_1(request()->param()),//游客数量
//            CountDashboard::getclient_2(request()->param()),//适用会员
//            CountDashboard::getclient_3(request()->param()),//正式会员

            CountDashboard::getclient_4(request()->param()),//论坛板块数量
            CountDashboard::getclient_5(request()->param()),//论坛文章数量

            CountDashboard::getclient_6(request()->param()),//新闻版块数量
            CountDashboard::getclient_7(request()->param()),//新闻数量



            CountDashboard::getclient_9(request()->param()),//直播广场版块数量
            CountDashboard::getclient_10(request()->param()),//直播广场内容数量

            CountDashboard::getclient_11(request()->param()),//成长计划版块数量
            CountDashboard::getclient_12(request()->param()),//成长计划内容数量

            CountDashboard::getclient_13(request()->param()),//量化辅助版块数量
            CountDashboard::getclient_14(request()->param()),//量化辅助内容数量

            CountDashboard::getclient_15(request()->param()),//数据库版块数量
            CountDashboard::getclient_16(request()->param()),//数据库内容数量

            CountDashboard::getclient_8(request()->param()),//首页浏览ip数量
        ];
        if ($this->request->isPost()) {
            $this->success('筛选成功', null, $client);
        }

        $res = array_merge($res, $client,$user_group);

        $this->view->assign($res);

        $this->assignconfig('column', array_keys($userlist));
        $this->assignconfig('userdata', array_values($userlist));

        return $this->view->fetch();
    }

    public function index_COPY()
    {
        try {
            \think\Db::execute("SET @@sql_mode='';");
        } catch (\Exception $e) {

        }
        $column = [];
        $starttime = Date::unixtime('day', -6);
        $endtime = Date::unixtime('day', 0, 'end');
        $joinlist = Db("user")->where('jointime', 'between time', [$starttime, $endtime])
            ->field('jointime, status, COUNT(*) AS nums, DATE_FORMAT(FROM_UNIXTIME(jointime), "%Y-%m-%d") AS join_date')
            ->group('join_date')
            ->select();
        for ($time = $starttime; $time <= $endtime;) {
            $column[] = date("Y-m-d", $time);
            $time += 86400;
        }
        $userlist = array_fill_keys($column, 0);
        foreach ($joinlist as $k => $v) {
            $userlist[$v['join_date']] = $v['nums'];
        }

        $dbTableList = Db::query("SHOW TABLE STATUS");
        $addonList = get_addon_list();
        $totalworkingaddon = 0;
        $totaladdon = count($addonList);
        foreach ($addonList as $index => $item) {
            if ($item['state']) {
                $totalworkingaddon += 1;
            }
        }
        $res = [
            'totaluser' => User::count(),
            'totaladdon' => $totaladdon,
            'totaladmin' => Admin::count(),
            'totalcategory' => \app\common\model\Category::count(),
            'todayusersignup' => User::whereTime('jointime', 'today')->count(),
            'todayuserlogin' => User::whereTime('logintime', 'today')->count(),
            'sevendau' => User::whereTime('jointime|logintime|prevtime', '-7 days')->count(),
            'thirtydau' => User::whereTime('jointime|logintime|prevtime', '-30 days')->count(),
            'threednu' => User::whereTime('jointime', '-3 days')->count(),
            'sevendnu' => User::whereTime('jointime', '-7 days')->count(),
            'dbtablenums' => count($dbTableList),
            'dbsize' => array_sum(array_map(function ($item) {
                return $item['Data_length'] + $item['Index_length'];
            }, $dbTableList)),
            'totalworkingaddon' => $totalworkingaddon,
            'attachmentnums' => Attachment::count(),
            'attachmentsize' => Attachment::sum('filesize'),
            'picturenums' => Attachment::where('mimetype', 'like', 'image/%')->count(),
            'picturesize' => Attachment::where('mimetype', 'like', 'image/%')->sum('filesize'),
        ];

        $client = [
            'client_1' => CountDashboard::getclient_1(request()->param()),//游客数量
            'client_2' => CountDashboard::getclient_2(request()->param()),//适用会员
            'client_3' => CountDashboard::getclient_3(request()->param()),//正式会员
            'client_4' => CountDashboard::getclient_4(request()->param()),//论坛板块数量
            'client_5' => CountDashboard::getclient_5(request()->param()),//版块主题帖数量
            'client_6' => CountDashboard::getclient_6(request()->param()),//新闻版块数量
            'client_7' => CountDashboard::getclient_7(request()->param()),//新闻数量
            'client_8' => CountDashboard::getclient_8(request()->param()),//首页浏览ip数量
        ];
        if ($this->request->isPost()) {
            $this->success('筛选成功', null, $client);
        }
        $res = array_merge($res, $client);
        $this->view->assign($res);

        $this->assignconfig('column', array_keys($userlist));
        $this->assignconfig('userdata', array_values($userlist));

        return $this->view->fetch();
    }

    //导出Execel
    public function exportExecle(){
        $date = $this->request->param('select_time','');
        if (!$date){
            $start_date = date("Y-m-d");
            $end_date = date("Y-m-d");
        }else{
            $date = explode(' - ',$date);
            $start_date = $date[0];
            $end_date = $date[1] ?? date("Y-m-d");
        }

        //循环得到每天的日期
        $current_date = strtotime($start_date);
        $end_date = strtotime($end_date);

        $date_arr = [];
        while ($current_date <= $end_date) {
            $date_arr[] = date('Y-m-d', $current_date);
            $current_date = strtotime('+1 day', $current_date);
        }

        $client = [];
        foreach($date_arr as $k=>$v){
            $s_date = "{$v}";
            $e_date = "{$v}";
            $select_time = "{$s_date} - {$e_date}";
            $client[] = [
                'date'=>$v,
                'client_1'=>CountDashboard::getclient_1(['select_time'=>$select_time]),
                'client_2'=>CountDashboard::getclient_2(['select_time'=>$select_time]),
                'client_3'=>CountDashboard::getclient_3(['select_time'=>$select_time]),
                'client_4'=>CountDashboard::getclient_4(['select_time'=>$select_time]),
                'client_5'=>CountDashboard::getclient_5(['select_time'=>$select_time]),
                'client_6'=>CountDashboard::getclient_6(['select_time'=>$select_time]),
                'client_7'=>CountDashboard::getclient_7(['select_time'=>$select_time]),
                'client_8'=>CountDashboard::getclient_8(['select_time'=>$select_time]),

                'client_9'=>CountDashboard::getclient_9(['select_time'=>$select_time]),
                'client_10'=>CountDashboard::getclient_10(['select_time'=>$select_time]),
                'client_11'=>CountDashboard::getclient_11(['select_time'=>$select_time]),
                'client_12'=>CountDashboard::getclient_12(['select_time'=>$select_time]),
                'client_13'=>CountDashboard::getclient_13(['select_time'=>$select_time]),
                'client_14'=>CountDashboard::getclient_14(['select_time'=>$select_time]),
                'client_15'=>CountDashboard::getclient_15(['select_time'=>$select_time]),
                'client_16'=>CountDashboard::getclient_16(['select_time'=>$select_time]),
            ];
        }
        //执行数据导出

        // 创建一个新的Excel对象
        $spreadsheet = new Spreadsheet();
        // 获取当前活动的sheet
        $sheet = $spreadsheet->getActiveSheet();
        // 添加自定义表头
        $sheet->fromArray([
            '日期',
            '游客数量',
            '试用会员数量',
            '正式会员数量',
            '论坛板块数量',
            '论坛文章数量',
            '新闻板块数量',
            '新闻文章数量',
            '首页浏览ip数量',//8

            '直播广场版块数量',
            '直播广场内容数量',
            '成长计划版块数量',
            '成长计划内容数量',
            '量化辅助(图表)版块数量',
            '量化辅助(图表)内容数量',
            '数据库版块数量',
            '数据库内容数量',

        ], null, 'A1');

        // 写入数据
        $row = 2;
        foreach ($client as $v) {
            $sheet->setCellValue('A' . $row, $v['date']);
            $sheet->setCellValue('B' . $row, $v['client_1']['count']);
            $sheet->setCellValue('C' . $row, $v['client_2']['count']);
            $sheet->setCellValue('D' . $row, $v['client_3']['count']);
            $sheet->setCellValue('E' . $row, $v['client_4']['count']);
            $sheet->setCellValue('F' . $row, $v['client_5']['count']);
            $sheet->setCellValue('G' . $row, $v['client_6']['count']);
            $sheet->setCellValue('H' . $row, $v['client_7']['count']);
            $sheet->setCellValue('I' . $row, $v['client_8']['count']);

            $sheet->setCellValue('J' . $row, $v['client_9']['count']);
            $sheet->setCellValue('K' . $row, $v['client_10']['count']);
            $sheet->setCellValue('L' . $row, $v['client_11']['count']);
            $sheet->setCellValue('M' . $row, $v['client_12']['count']);
            $sheet->setCellValue('N' . $row, $v['client_13']['count']);
            $sheet->setCellValue('O' . $row, $v['client_14']['count']);
            $sheet->setCellValue('P' . $row, $v['client_15']['count']);
            $sheet->setCellValue('Q' . $row, $v['client_16']['count']);
            $row++;
        }

        // 生成Excel文件
        $excelDirectory = 'export_execle/' . date('Ymd') . '/';
        $excelFileName = 'Ex_' . time() . '.xlsx'; // 使用时间戳确保唯一性
        $filePath = $excelDirectory . $excelFileName;

// 创建目录（如果不存在）
        if (!is_dir($excelDirectory)) {
            mkdir($excelDirectory, 0777, true);
        }

// 保存文件
        $writer = new Xlsx($spreadsheet);
        $writer->save($filePath);

// 返回文件下载地址
        // 获取当前请求的域名
        $domain = $this->request->domain();
        return [
            'code' => 200,
            'url' => $domain . '/' . $filePath,
            'file_name' => $excelFileName,
        ];
    }

}
